package com.flyaudio.xiaopengautopilot.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.flyaudio.xiaopengautopilot.entity.AvgConsumption;
import com.flyaudio.xiaopengautopilot.entity.Consumption;
import com.flyaudio.xiaopengautopilot.entity.Sum;

import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

public  class DBManager {
    private static final String TAG = "DBManager";

    //db name
    public static final String DataBase_path = "flyaudio.db";
    public static final String DataBase_path_BACK = "/storage/emulated/0/FlyCarMsg/iFlyPC/flyaudio.db";
    //table version
    private static final int dbVersion = 5;

    //table name
    private static final String Consumption_Table = "consumption";
    private static final String Sum_Table = "sum";
    private static final String AVG_Consumption_Table = "avgconsumption";
    //consumption table key
    private static final String Key_ID = "_id";
    private static final String Key_Date = "date";
    private static final String Key_Mileage = "mileage";
    private static final String Key_TotalMileage = "totalMileage";
    private static final String Key_PowerConsumption = "powerConsumption";

    //sum table key
    private static final String Key_SumID = "_sumId";
    private static final String Key_SumDate = "sumDate";
    private static final String Key_SumMileage = "sumMileage";
    private static final String Key_SumTotalMileage = "sumTotalMileage";
    private static final String Key_SumPowerConsumption = "sumPowerConsumption";
    //AVG consumption table key
    private static final String Key_AVGID = "_avgid";
    private static final String Key_AVGTotalMileage = "totalMileage";
    private static final String Key_AVGPowerConsumption = "powerConsumption";
    private static final String Key_AVGDate = "avgDate";
    //create Consumption table SQL
    private static final String Consumption_DB_CREATE =
            "create table if not exists " + Consumption_Table + " (" + Key_ID + " integer primary key,"
                    + Key_Date + " DATE," + Key_PowerConsumption + " TEXT," + Key_TotalMileage + " TEXT,"
                    + Key_Mileage + " TEXT)";
    //create AVGConsumption table SQL
    private static final String AVG_Consumption_DB_CREATE =
            "create table if not exists " + AVG_Consumption_Table + " (" + Key_AVGID + " integer primary key,"
                   + Key_AVGTotalMileage + " TEXT,"+ Key_AVGDate +" DATE ,"+Key_AVGPowerConsumption + " TEXT)";
    //create Sum table SQL
    private static final String Sum_DB_CREATE =
            "create table if not exists " + Sum_Table + " (" + Key_SumID + " integer primary key,"
                    + Key_SumDate + " DATE unique," + Key_SumMileage + " TEXT," + Key_SumTotalMileage + " TEXT,"
                    + Key_SumPowerConsumption + " TEXT)";

    private DBHelper dbHelper;

    public DBManager(Context context, String dbPath) {
        dbHelper = new DBHelper(context, context.getDatabasePath(dbPath).getAbsolutePath());
    }

    /**
     * insert data consumption table SQL
     *插入表Consumption数据
     * @param consumption
     */
    public  void insert(Consumption consumption) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "insert into consumption(date,mileage,totalMileage,powerConsumption) values (?,?,?,?)";
        Object[] obj = new Object[]{consumption.getDate(), consumption.getMileage(), consumption.getTotalMileage(), consumption.getPowerConsumption()};
        db.execSQL(sql, obj);

    }

    @Override
    protected void finalize() throws Throwable {
        dbHelper.close();
        super.finalize();
    }

    /**
     * insert data sum table SQL
     *插入表Sum数据
     * @param sum
     */
    public  void insert(Sum sum) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Key_SumDate,sum.getSumDate());
        values.put(Key_SumMileage,sum.getSumMileage());
        values.put(Key_SumTotalMileage,sum.getSumTotalMileage());
        values.put(Key_SumPowerConsumption,sum.getSumPowerConsumption());
        db.insertWithOnConflict(Sum_Table,null,values,SQLiteDatabase.CONFLICT_REPLACE);
        values.clear();
        
    }
    public  void insert(AvgConsumption consumption) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "insert into "+AVG_Consumption_Table+" ("+Key_AVGTotalMileage+","+Key_AVGPowerConsumption+","+Key_AVGDate+") values (?,?,?)";
        Object[] obj = new Object[]{consumption.getTotalMileage(), consumption.getPowerConsumption(),consumption.getDate()};
        db.execSQL(sql, obj);

    }
    /**
     * update data consumption table SQL where _id
     *更新表Consumption数据
     * @param consumption
     */
    public  void update(Consumption consumption) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "update consumption set date=?,mileage=?,totalMileage = ?,powerConsumption = ? where _id= ?";
        Object[] obj = new Object[]{consumption.getDate(), consumption.getMileage(), consumption.getTotalMileage(), consumption.getPowerConsumption(), consumption.get_id()};
        db.execSQL(sql, obj);
        
    }

    /**
     * update data sum table SQL where _sumId
     *更新表Sum数据
     * @param sum
     */
    public  void update(Sum sum) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "update sum set sumDate=?,sumMileage=?,sumTotalMileage = ?,sumPowerConsumption = ? where _sumId= ?";
        Object[] obj = new Object[]{sum.getSumDate(), sum.getSumMileage(), sum.getSumTotalMileage(), sum.getSumPowerConsumption(), sum.get_sumId()};
        db.execSQL(sql, obj);
        
    }

    /**
     * 删除Sum某段日期的数据
     * @param date
     */
    public  void deleteSumByDateBefore(String date) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "delete from sum where "+ Key_SumDate+" < '"+ date+"'";
        db.execSQL(sql);
        
    }
    /**
     * query  last count Data consumption table SQL
     * 查询表Consumption最后一条数据
     * @return list
     */
    public  ArrayList<Consumption> queryConsumptionLastOfNum(int count) {
        ArrayList<Consumption> list = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //Cursor是结果集游标，使用Cursou.moveToNext()方法可以从当前行移动到下一行
        String sql = "SELECT _id," + " date ," + " mileage ," + " totalMileage," + " powerConsumption" + " FROM consumption order by totalMileage DESC limit ?";
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(count)});
        int clos_len = cursor.getColumnCount();  //获取数据所有列数

        while (cursor.moveToNext()) {  //循环表格中的每一行
            Consumption consumption = new Consumption();
            consumption.set_id(cursor.getLong(cursor.getColumnIndex(Key_ID)));
            consumption.setDate(cursor.getString(cursor.getColumnIndex(Key_Date)));
            consumption.setPowerConsumption(cursor.getString(cursor.getColumnIndex(Key_PowerConsumption)));
            consumption.setMileage(cursor.getString(cursor.getColumnIndex(Key_Mileage)));
            consumption.setTotalMileage(cursor.getString(cursor.getColumnIndex(Key_TotalMileage)));
            list.add(consumption);
        }
        cursor.close();
        
        return list;
    }

    /**
     *查询表Sum某一段日期里程、电耗范围
     * @param begin
     * @param end
     * @return
     */
    public  HashMap<String,Object> queryPowerSumInMileageRange(float begin,float end) {

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //Cursor是结果集游标，使用Cursou.moveToNext()方法可以从当前行移动到下一行
        String sql = "SELECT SUM(mileage) AS mile, SUM(powerConsumption) AS power" + " FROM consumption WHERE totalMileage >= ? AND totalMileage <= ?";
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(begin),String.valueOf(end)});

        HashMap<String,Object> map = new HashMap<>();
        if (cursor.moveToNext()) {  //循环表格中的每一行

            map.put("mile",cursor.getFloat(cursor.getColumnIndex("mile")));
            map.put("power",cursor.getFloat(cursor.getColumnIndex("power")));
        }
        cursor.close();
        
        return map;
    }
    /**
     * query lastData consumption table SQL
     *查询表Consumption最后一条数据
     * @return
     */
    public  HashMap<String, Object> queryLastRecord() {
        HashMap<String, Object> map = new HashMap<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select * from consumption order by _id DESC limit 1";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            map.put(Key_ID, cursor.getInt(cursor.getColumnIndex(Key_ID)));
            map.put(Key_Date, cursor.getString(cursor.getColumnIndex(Key_Date)));
            map.put(Key_Mileage, cursor.getString(cursor.getColumnIndex(Key_Mileage)));
            map.put(Key_TotalMileage, cursor.getString(cursor.getColumnIndex(Key_TotalMileage)));
            map.put(Key_PowerConsumption, cursor.getString(cursor.getColumnIndex(Key_PowerConsumption)));
        }
        cursor.close();
        
        return map;
    }

    /**
     * query lastData sum table SQL
     *查询表Sum最后一条数据
     * @return
     */
    public  HashMap<String, Object> querySumLastRecord() {
        HashMap<String, Object> map = new HashMap<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select * from sum order by _sumId DESC limit 1";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            map.put(Key_SumID, cursor.getInt(cursor.getColumnIndex(Key_SumID)));
            map.put(Key_SumDate, cursor.getString(cursor.getColumnIndex(Key_SumDate)));
            map.put(Key_SumMileage, cursor.getString(cursor.getColumnIndex(Key_SumMileage)));
            map.put(Key_SumTotalMileage, cursor.getString(cursor.getColumnIndex(Key_SumTotalMileage)));
            map.put(Key_SumPowerConsumption, cursor.getString(cursor.getColumnIndex(Key_SumPowerConsumption)));
        }
        cursor.close();
        
        return map;
    }

    /**
     * query data sum table SQL where sumDate
     *根据时间来查询表Sum某一条数据
     * @param sumDate
     * @return
     */
    public  Sum querySumDateItem(String sumDate) {
        Sum sum = null;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select _sumId ,sumMileage , sumTotalMileage, sumPowerConsumption from sum where sumDate=?";
        Cursor cursor = db.rawQuery(sql, new String[]{sumDate});
        if (cursor.moveToNext()) {
            sum = new Sum();
            sum.set_sumId(cursor.getInt(0));
            sum.setSumMileage(cursor.getString(1));
            sum.setSumTotalMileage(cursor.getString(2));
            sum.setSumPowerConsumption(cursor.getString(3));
        }
        cursor.close();
        
        return sum;
    }

    /***
     *  查询表Sum最近的日期
     * @return
     */
    public  String querySumDateMax(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "SELECT MAX("+Key_SumDate+") AS max FROM sum";
        Cursor cursor = db.rawQuery(sql, null);
        String date = null;
        if (cursor.moveToNext()){
            date = cursor.getString(cursor.getColumnIndex("max"));

        }
        cursor.close();
        
        return date;
    }
    /***
     *  查询表Sum最远的日期
     * @return
     */
    public  String querySumDateMin(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "SELECT MIN("+Key_SumDate+") AS min FROM sum";
        Cursor cursor = db.rawQuery(sql, null);
        String date = null;
        if(cursor.moveToNext()){
            date = cursor.getString(cursor.getColumnIndex("min"));
        }
        cursor.close();
  
        return date;
    }
    /***
     *  查询表Sum某一段日期的数据
     * @return
     */
    public  List<Sum> querySumInDate(String firstDate, String lastDate){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        ArrayList<Sum> list = new ArrayList<>();
        String sql = "select * from "+Sum_Table+" where "+Key_SumDate+" Between '"+firstDate+"' and  '"+lastDate
                + "' order by "+Key_SumDate+" ASC";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            Sum sum = new Sum();
            sum.set_sumId(cursor.getInt(cursor.getColumnIndex(Key_SumID)));
            sum.setSumDate(cursor.getString(cursor.getColumnIndex(Key_SumDate)));
            sum.setSumMileage(cursor.getString(cursor.getColumnIndex(Key_SumMileage)));
            sum.setSumTotalMileage(cursor.getString(cursor.getColumnIndex(Key_SumTotalMileage)));
            sum.setSumPowerConsumption(cursor.getString(cursor.getColumnIndex(Key_SumPowerConsumption)));
            list.add(sum);
        }
        cursor.close();

        return list;
    }
    /***
     *  统计表Sum某一段日期的电耗
     * @return
     */
     
    public  float queryPowerConsumptionInDate(String firstDate, String lastDate){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        ArrayList<Sum> list = new ArrayList<>();
        String sql = "select SUM("+Key_SumPowerConsumption+") AS Consumption from "+Sum_Table+" where "+Key_SumDate+" Between '"+firstDate+"' and  '"+lastDate
                + "'";
        Cursor cursor = db.rawQuery(sql, null);
        float Consumption = 0;
        if (cursor.moveToNext()) {
            Consumption = cursor.getFloat(cursor.getColumnIndex("Consumption"));

        }
        cursor.close();
        
        return Consumption;
    }

    /**
     * 查询表Sum平均电耗
     * @return
     */
    public  float queryPowerConsumptionAvg(){
        HashMap<String, Object> map = new HashMap<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select SUM("+Key_SumPowerConsumption+") AS Consumption ,SUM("+Key_SumMileage+") AS Mileage from sum";
        Cursor cursor = db.rawQuery(sql, null);
        float consumption = 0;
        float mileage = 1;
        while (cursor.moveToNext()) {
            consumption = cursor.getFloat(cursor.getColumnIndex("Consumption"));
            mileage = cursor.getFloat(cursor.getColumnIndex("Mileage"));
        }
        cursor.close();
        
        if (mileage != 0){
            return (consumption*100/mileage);
        }
        return 0;
    }

    /**
     * 查询表Sum某一段日期的平均电耗
     * @param begin
     * @param end
     * @return
     */
    public  float queryPowerConsumptionAvgInDate(String begin,String end){
        HashMap<String, Object> map = new HashMap<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select SUM("+Key_SumPowerConsumption+") AS Consumption ,SUM("+Key_SumMileage+") AS Mileage from sum "
                +" where "+Key_SumDate+" Between '"+begin+"' and  '"+end+ "'";
        Cursor cursor = db.rawQuery(sql, null);
        float consumption = 0;
        float mileage = 1;
        while (cursor.moveToNext()) {
            consumption = cursor.getFloat(cursor.getColumnIndex("Consumption"));
            mileage = cursor.getFloat(cursor.getColumnIndex("Mileage"));
        }
        cursor.close();
        
        if (mileage != 0){
            return (consumption*100/mileage);
        }
        return 0;
    }
    /**
     *查询表某一段里电耗/百公里
     * @param begin
     * @param end
     * @return
     */
    public  HashMap<String,Object> queryAvgPowerSumInMileageRange(float begin,float end) {

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //Cursor是结果集游标，使用Cursou.moveToNext()方法可以从当前行移动到下一行
        String sql = "SELECT AVG(powerConsumption) AS power FROM " + AVG_Consumption_Table+  " WHERE totalMileage >= ? AND totalMileage <= ?";
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(begin),String.valueOf(end)});

        HashMap<String,Object> map = new HashMap<>();
        if (cursor.moveToNext()) {  //循环表格中的每一行

            map.put("power",cursor.getFloat(cursor.getColumnIndex("power")));
        }
        cursor.close();

        return map;
    }
    /**
     * query  last count Data consumption table SQL
     * 查询表Consumption最后一条数据
     * @return list
     */
    public  ArrayList<AvgConsumption> queryAvgConsumptionLastOfNum(int count) {
        ArrayList<AvgConsumption> list = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //Cursor是结果集游标，使用Cursou.moveToNext()方法可以从当前行移动到下一行
        String sql = "SELECT " + Key_AVGPowerConsumption + "," + " totalMileage" + " FROM "+AVG_Consumption_Table + " order by totalMileage DESC limit ?";
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(count)});
        int clos_len = cursor.getColumnCount();  //获取数据所有列数

        while (cursor.moveToNext()) {  //循环表格中的每一行
            AvgConsumption consumption = new AvgConsumption();
            consumption.setPowerConsumption(cursor.getString(1));
            consumption.setTotalMileage(cursor.getString(0));
            list.add(consumption);
        }
        cursor.close();

        return list;
    }
    public  HashMap<String,Object> queryAVGInMonth(String firstDate, String lastDate){
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        String sql = "select AVG(powerConsumption) AS power from "+AVG_Consumption_Table+" where "+Key_AVGDate+" Between '"+firstDate+"' and  '"+lastDate
                + "'";
        Cursor cursor = db.rawQuery(sql, null);
        HashMap<String,Object> map = new HashMap<>();
        if (cursor.moveToNext()) {  //循环表格中的每一行

            map.put("power",cursor.getFloat(cursor.getColumnIndex("power")));
        }
        cursor.close();

        return map;
    }
    public  HashMap<String,Object> queryAVGInTotal(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        String sql = "select AVG(powerConsumption) AS power from "+AVG_Consumption_Table;
        Cursor cursor = db.rawQuery(sql, null);
        HashMap<String,Object> map = new HashMap<>();
        if (cursor.moveToNext()) {  //循环表格中的每一行

            map.put("power",cursor.getFloat(cursor.getColumnIndex("power")));
        }
        cursor.close();

        return map;
    }
    /**
     * 重置表Sum数据
     */
    public  void resetSumTable(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("DROP TABLE " + Sum_Table + ";");
        db.execSQL(Sum_DB_CREATE);
        
    }

    /**
     * 重置表Consumption数据
     */
    public  void resetConsumptionTable(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("DROP TABLE " + Consumption_Table + ";");
        db.execSQL(Consumption_DB_CREATE);
        
    }
    /**
     * 重置表Consumption数据
     */
    public  void resetAVGTable(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("DROP TABLE " + AVG_Consumption_Table + ";");
        db.execSQL(AVG_Consumption_DB_CREATE);

    }
    public class DBHelper extends SQLiteOpenHelper {
        public  DBHelper(Context context, String dbPath) {
            super(context, dbPath, null, dbVersion);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(Consumption_DB_CREATE);
            db.execSQL(Sum_DB_CREATE);
            db.execSQL(AVG_Consumption_DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            onCreate(db);
        }
    }

}
